# 导入包
from matplotlib import pyplot as plt
import pandas as pd
import statsmodels.formula.api as smf
from sqlalchemy import create_engine
import pymysql
import statsmodels.api as sm
import numpy as np
import seaborn as sns

# 数据库配置
db_config = {
    'host': '127.0.0.1',
    'user': 'root',
    'password': 'root',
    'database': 'tushare',
    'port': 3306,
    'charset': 'utf8mb4'
}

# 创建数据库连接
engine = create_engine(
    f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}"
)
conn = pymysql.connect(**db_config)
chunk_size = 10000

# 从数据库读取数据（修正SQL语法和字段标点）
df = pd.read_sql_query("""
    SELECT d.*FROM date_1 d WHERE d.trade_date BETWEEN '2023-01-01' AND '2023-12-31' AND d.ts_code = '000001.SZ'
    """,
    conn,
    chunksize=chunk_size)
df1 = pd.concat(df, ignore_index=True)

# 新增股票涨跌幅计算
df1['zd_closes'] = round((df1['closes'] - df1['closes'].shift(1)) / df1['closes'].shift(1), 2)

# 处理缺失值（修正方法名和参数）
df1 = df1.dropna(subset=['zd_closes'])
print(df1.head())

# 筛选自变量（修正数据类型筛选）
ex = ['zd_closes', 'id', 'ts_code', 'trade_date', 'the_date', 'opens', 'high', 'low', 
      'closes', 'pre_closes', 'changes', 'pct_chg', 'amount']
numeric_cols = df1.select_dtypes(include=['number']).columns.tolist()

# 构建回归公式（修正公式格式）
newlist = [col for col in numeric_cols if col not in ex]
formuls = 'zd_closes ~ ' + ' + '.join(newlist)  

res = smf.ols(formuls, data=df1).fit()
print(model.summary())
